/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.bean.SchoolItemBean;
import com.orange.modules.entity.AuthItem;
import com.orange.modules.entity.expert.AuthExam;
import com.orange.modules.entity.expert.AuthExpertItemMap;
import com.orange.modules.entity.expert.AuthExpertSchoolMap;

/**
 * @author gaogs
 *
 * 2016-12-15
 */
@Repository
public class AuthExpertSchoolMapRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertSchoolMapDao authExpertSchoolMapDao;
	
	/**
	 * 材料评审——保存专家与学校的关系
	 * @param list
	 */
	public void saveAuthExpertSchoolMapList(List<AuthExpertSchoolMap>list){
		authExpertSchoolMapDao.save(list);
	}
	
	/**
	 * 材料评审——根据专家id删除专家与学校的关系
	 * @param pid
	 * @throws SQLException 
	 */
	public void deleteAuthExpertSchoolMapInfo(String expertId) throws SQLException{
		String sql="DELETE FROM t_auth_school_expert_map WHERE s_expert_id=?";
		dbHelper.execute(sql, expertId);
	}
	
	/**
	 * 根据专家id查询专家绑定的学校
	 * @param expertId
	 * @return
	 * @throws SQLException
	 */
	public List<AuthExpertSchoolMap> queryAuthExpertSchoolMapInfoByExpert(String expertId) throws SQLException{
		String sql="select a.p_id as pid ,a.s_school_id as schoolId ,a.s_expert_id as expertId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime ,b.s_school_name as schoolName" +
				" FROM t_auth_school_expert_map as a left join t_auth_school as b ON a.s_school_id=b.p_id WHERE a.s_expert_id =?";
		List<AuthExpertSchoolMap> list=dbHelper.getBeanList(sql, AuthExpertSchoolMap.class, expertId);
		return list;
	}

	
	/**
	 * 分页查询材料评审专家绑定的学校
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthExpertSchoolMap> queryAuthExamPage(Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT a.p_id as pid ,a.s_school_id as schoolId ," +
				" a.s_expert_id as expertId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime " +
				" FROM t_auth_school_expert_map as a WHERE a.s_expert_id='"+map.get("expertId")+"'");
		
		sql.append("  ORDER BY aa.s_major,aa.s_task_id, aa.s_number ASC ");
		Page<AuthExpertSchoolMap> authExpertSchoolMaps = dbHelper.getPage(sql.toString(),AuthExpertSchoolMap.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return authExpertSchoolMaps;
	}
	
	
	/**
	 * 分页查询材料评审专家绑定的学校
	 * 
	 */
	public Page<SchoolItemBean> queryAuthSchoolPageByExportId(String exportId ,Map<String, String> map) throws SQLException {
		String sql = "SELECT TOP 1000 c.p_id as schoolId, c.s_school_name as schoolName ,c.s_major as major ,a.s_group_id as groupId"+
				 " FROM t_auth_school_expert_map as a"+
				 //" LEFT JOIN t_auth_item as b ON a.s_item_id = b.p_id "+
				 " LEFT JOIN t_auth_school as c ON a.s_school_id = c.p_id "+
				 " WHERE a.s_expert_id = '"+exportId+"'";
		Page<SchoolItemBean> page = dbHelper.getPage(sql.toString(),SchoolItemBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
	
	/**
	 * 根据专家id查询专家绑定的学校
	 * @param expertId
	 * @return
	 * @throws SQLException
	 */
	public AuthExpertSchoolMap queryAuthExpertSchoolMapInfoByExpert1(String expertId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_school_id as schoolId ,a.s_expert_id as expertId ," +
				" a.s_group_id as groupId ,a.s_create_time as createTime ,b.s_school_name as schoolName" +
				" FROM t_auth_school_expert_map as a LEFT　JOIN t_auth_school as b ON a.s_school_id=b.p_id WHERE a.s_expert_id =?";
		AuthExpertSchoolMap list= dbHelper.getBean(sql, AuthExpertSchoolMap.class, expertId);
		return list;
	}
	
	/**
	 * 查看一级指标材料评审分项意见记录表中的学校对应的指标
	 * @throws SQLException 
	 */
	public List<AuthItem> queryAuthItemBySchoolIdAndExpertId(String schoolId, String expertId) throws SQLException{
		String sql = "Select b.p_id, b.s_content as content, b.s_number as number,a.s_state as state from t_auth_item_expert_map as a " +
				"left join t_auth_item as b on a.s_item_id = b.p_id where a.s_school_id =? and a.s_expert_id =? order by b.s_number";
		return dbHelper.getBeanList(sql, AuthItem.class, schoolId, expertId);
		
	}
	
	/**
	 * 分页查询材料评审专家绑定的学校
	 * 
	 */
	public Page<SchoolItemBean> queryAuthSchoolPageByGroupId(String expertId ,Map<String, String> map) throws SQLException {
		String sql = "SELECT DISTINCT TOP 1000 c.p_id as schoolId, c.s_school_name as schoolName ,c.s_major as major ,a.s_group_id as groupId, b.s_collect as collect, b.s_isfollow as isfollow"+
				 " FROM t_auth_school as c"+
				 //" LEFT JOIN t_auth_item as b ON a.s_item_id = b.p_id "+
				 " LEFT JOIN t_auth_school_expert_map as a ON a.s_school_id = c.p_id " +
				 " left join t_auth_school_group_map as b on b.s_school_id = c.p_id "+
				 " WHERE a.s_group_id = " +
				 "(select t.s_group_id as groupId from t_auth_export_group_map as t left join t_auth_group as b on t.s_group_id = b.p_id where b.s_state != 3 and t.s_type = 0 and t.s_export_id = '"+expertId+"')";
		Page<SchoolItemBean> page = dbHelper.getPage(sql.toString(),SchoolItemBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
	
	/**
	 * 根据组id查询学校id
	 * @throws SQLException 
	 */
	public List<AuthExpertSchoolMap> querySchoolIdByGroupId(String groupId) throws SQLException{
		String sql = "select DISTINCT s_school_id from t_auth_school_expert_map where s_group_id =?";
		return dbHelper.getBeanList(sql, AuthExpertSchoolMap.class, groupId);
	}
	
	/**
	 * 分页查询材料评审专家绑定的学校
	 * 
	 */
	public Page<SchoolItemBean> queryAuthSchoolPageByGroupId1(String groupId ,Map<String, String> map) throws SQLException {
		String sql = "SELECT DISTINCT TOP 1000 c.p_id as schoolId, c.s_school_name as schoolName ,c.s_major as major ,a.s_group_id as groupId, b.s_collect as collect, b.s_isfollow as isfollow"+
				 " FROM t_auth_school as c"+
				 //" LEFT JOIN t_auth_item as b ON a.s_item_id = b.p_id "+
				 " LEFT JOIN t_auth_school_expert_map as a ON a.s_school_id = c.p_id " +
				 " left join t_auth_school_group_map as b on b.s_school_id = c.p_id "+
				 " WHERE a.s_group_id = '"+groupId+"'";
		Page<SchoolItemBean> page = dbHelper.getPage(sql.toString(),SchoolItemBean.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return page;
	}
}
